const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql2/promise');
// 创建一个Express应用
const app = express();
// 使用body-parser中间件处理JSON数据
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

//允许所有域调用
app.use(function(req, res, next) {
  res.header("Access-Control-Allow-Origin", "*"); //匹配请求来源，第二个参数是前端应用域名
  res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
  next();
});

//想要指定多个可信来源，可以这样做
// const allowedOrigins = ['https://example.com', 'http://localhost:3000'];
// app.use(function(req, res, next) {
//   const origin = req.headers.origin;
//   if (allowedOrigins.indexOf(origin) > -1) {
//     res.header("Access-Control-Allow-Origin", origin);
//   }
//   res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
//   next();
// });


// 数据库配置
//自己修改！！！
const dbConfig = {
    host: 'localhost',
    port: 3306,
    user: 'USER',
    password: 'PASSWORD',
    database: 'city'
};



// 创建一个函数来检查数据库连接
function checkDatabaseConnection() {
    return new Promise((resolve, reject) => {
        mysql.createConnection(dbConfig)
            .then(connection => {
                return connection.execute('SELECT 1+1 AS result')
                    .then(() => {
                        connection.end();
                        resolve();
                    })
                    .catch(error => {
                        reject(error);
                    });
            })
            .catch(error => {
                reject(error);
            });
    })
    .then(() => {
        console.log('Database connection successful');
    })
    .catch(error => {
        console.error('Error connecting to the database:', error);
        process.exit(1);
    });
}

// 创建一个函数来查找城市ID
function findCityIdByName(city) {
    return new Promise((resolve, reject) => {
        mysql.createConnection(dbConfig)
            .then(connection => {
                // 在这里打印查询参数
                console.log(`Searching for city: ${city}`);
                
                
                return connection.execute(
                    //模糊匹配，一旦匹配到前端传来的数据一部分，就返回city_index
                    'SELECT city_index FROM city WHERE city LIKE ? LIMIT 1',
                    ['%' + city + '%']
                )
                    .then(([rows]) => {
                        connection.end();
                        if (rows.length > 0) {
                            const cityIndex = rows[0].city_index;

                            return mysql.createConnection(dbConfig)
                                .then(connection => {
                                    return connection.execute(
                                        //接收到city_index之后将city_id返回给前端应用，至此后端服务器作用结束
                                        'SELECT city_id FROM city WHERE city_index = ?',
                                        [cityIndex]
                                    )
                                        .then(([cityIdRows]) => {
                                            connection.end();
                                            if (cityIdRows.length > 0) {
                                                resolve(cityIdRows[0].city_id);
                                            } else {
                                                resolve(null);
                                            }
                                        })
                                        .catch(reject);
                                })
                                .catch(reject);
                        } else {
                            resolve(null);
                        }
                    })
                    .catch(reject);
            })
            .catch(reject);
    });
}

// 创建一个路由来处理城市匹配，这里的'/match-city'可以随意修改，但需同步修改前端应用index.html里serverUrl参数对应部分
app.get('/match-city', (req, res) => {
    const city = req.query.city;
    findCityIdByName(city)
        .then(cityId => {
            if (cityId) {
                res.json({ city_id: cityId });
            } else {
                res.status(404).json({ error: 'City not found' });
            }
        })
        .catch(error => {
            res.status(500).json({ error: 'Internal server error' });
        });
});

// 在启动服务器之前检查数据库连接
checkDatabaseConnection();

// 启动服务器
const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
    console.log(`Server is running on port ${PORT}`);
});